﻿create database QuanLyBanHang
go
use QuanLyBanHang
go
CREATE TABLE [dbo].[CTDonHang](
	[MaDH] [char](10) NOT NULL,
	[MaSP] [char](10) NOT NULL,
	[NgayBan] [datetime] NOT NULL,
	[TenSP] [nchar](50) NULL,
	[SoLuong] [int] NULL,
	[DonGia] [float] NULL,
	[ThanhTien] [float] NULL,
 CONSTRAINT [PK_CTDonHang] PRIMARY KEY CLUSTERED 
(
	[MaDH] ASC,
	[MaSP] ASC,
	[NgayBan] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[DanhMucSP](
	[MaDM] [char](10) NOT NULL,
	[TenDM] [nchar](20) NULL,
 CONSTRAINT [PK_DanhMucSP] PRIMARY KEY CLUSTERED 
(
	[MaDM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[DonHang](
	[MaDH] [char](10) NOT NULL,
	[NgayBan] [datetime] NOT NULL,
	[MaKH] [char](10) NULL,
	[TenKH] [nchar](20) NULL,
	[DiaChi] [nchar](50) NULL,
	[ThanhTien] [float] NULL,
 CONSTRAINT [PK_DonHang] PRIMARY KEY CLUSTERED 
(
	[MaDH] ASC,
	[NgayBan] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[KhachHang](
	[MaKH] [char](10) NOT NULL,
	[TenKH] [nchar](20) NULL,
	[DienThoai] [nvarchar](12) NULL,
	[DiaChi] [nvarchar](50) NULL,
 CONSTRAINT [PK_KhachHang] PRIMARY KEY CLUSTERED 
(
	[MaKH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[LoaiNV](
	[MaLoai] [char](10) NOT NULL,
	[TenLoai] [nvarchar](20) NULL,
 CONSTRAINT [PK_LoaiNV] PRIMARY KEY CLUSTERED 
(
	[MaLoai] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[NhanVien](
	[MaNV] [char](10) NOT NULL,
	[TenNV] [nvarchar](20) NOT NULL,
	[MatKhau] [nchar](10) NOT NULL,
	[Loai] [char](10) NOT NULL,
 CONSTRAINT [PK_NhanVien] PRIMARY KEY CLUSTERED 
(
	[MaNV] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[SanPham](
	[MaSP] [char](10) NOT NULL,
	[TenSp] [nvarchar](50) NULL,
	[DonGia] [float] NULL,
	[SoLuong] [int] NULL,
	[DanhMuc] [char](10) NULL,
	[Hinh] [image] NULL,
 CONSTRAINT [PK_SanPham] PRIMARY KEY CLUSTERED 
(
	[MaSP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[CTDonHang]  WITH CHECK ADD  CONSTRAINT [FK_CTDonHang_DonHang] FOREIGN KEY([MaDH], [NgayBan])
REFERENCES [dbo].[DonHang] ([MaDH], [NgayBan])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CTDonHang] CHECK CONSTRAINT [FK_CTDonHang_DonHang]
GO
ALTER TABLE [dbo].[CTDonHang]  WITH CHECK ADD  CONSTRAINT [FK_CTDonHang_SanPham] FOREIGN KEY([MaSP])
REFERENCES [dbo].[SanPham] ([MaSP])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CTDonHang] CHECK CONSTRAINT [FK_CTDonHang_SanPham]
GO
ALTER TABLE [dbo].[DonHang]  WITH CHECK ADD  CONSTRAINT [FK_DonHang_KhachHang] FOREIGN KEY([MaKH])
REFERENCES [dbo].[KhachHang] ([MaKH])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[DonHang] CHECK CONSTRAINT [FK_DonHang_KhachHang]
GO
ALTER TABLE [dbo].[NhanVien]  WITH CHECK ADD  CONSTRAINT [FK_NhanVien_LoaiNV] FOREIGN KEY([Loai])
REFERENCES [dbo].[LoaiNV] ([MaLoai])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[NhanVien] CHECK CONSTRAINT [FK_NhanVien_LoaiNV]
GO
ALTER TABLE [dbo].[SanPham]  WITH CHECK ADD  CONSTRAINT [FK_SanPham_DanhMucSP] FOREIGN KEY([DanhMuc])
REFERENCES [dbo].[DanhMucSP] ([MaDM])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SanPham] CHECK CONSTRAINT [FK_SanPham_DanhMucSP]
GO
insert into LoaiNV values('L000000001',N'Admin')
insert into LoaiNV values('L000000002',N'Kế Toán')
insert into LoaiNV values('L000000003',N'CSKH')
insert into LoaiNV values('L000000004',N'Ngân hàng')

insert into NhanVien values('NV00000001',N'Nguyen Huu Buoi','123456','L000000004')
insert into NhanVien values('NV00000002',N'Nguyen Huu Buoi','123456','L000000003')
insert into NhanVien values('NV00000003',N'Nguyen Huu Buoi','123456','L000000002')
insert into NhanVien values('NV00000004',N'Nguyen Huu Buoi','123456','L000000001')

insert into DanhMucSP values('DM00000001',N'Giáo Dục')
insert into DanhMucSP values('DM00000002',N'Hàng gia dụng')
insert into DanhMucSP values('DM00000003',N'Hàng điện tử')
insert into DanhMucSP values('DM00000004',N'Thời trang')


insert into SanPham values ('SP00000001',N'Bàn ủi Powerful Steam Brush','100000','2','DM00000002',NULL)
insert into SanPham values ('SP00000002',N'Bộ làm tóc SOKANY','100000','2','DM00000004',NULL)
insert into SanPham values ('SP00000003',N'Anh Ngữ Smart','100000','2','DM00000001',NULL)
insert into SanPham values ('SP00000004',N'MAC Book pro','1000000','2','DM00000003',NULL)

insert into KhachHang values('KH00000001',N'Mai Tuấn Hào','0939098980',N'45 Trương Định Q1')
insert into KhachHang values('KH00000002',N'Trương Mỹ Dung','0909212321',N'222 Nguyễn Trãi Q5')
insert into KhachHang values('KH00000003',N'Hà Anh Dũng','0934122321',N'78 Tháp Mười Q6')

insert into DonHang values('DH00000001','09/22/2013','KH00000001',N'Mai Tuấn Hào',N'45 Trương Định Q1','100000')

insert into CTDonHang values('DH00000001','SP00000001','09/22/2013',N'Bàn ủi Powerful Steam Brush','1','100000','100000')	

